﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using Bing.Toolkit.Data;
using Bing.Toolkit.Data.SQL;

namespace Bing.Studio
{
    public interface DataStructuresHelper
    {
        List<string> GetTalbeNameArray();
        List<string> GetViewNameArray();
        List<string> GetStoredProcedureNamesArray();
        TableObject GetTableInfomation(string strTableName);
        DataTable Execute(string strSqlText);
    }

    public class SQLServerDataStructuresHelper : DataStructuresHelper
    {
        public string ConnectionString { get; set; }

        public SQLServerDataStructuresHelper(string strConnectionString)
        {
            ConnectionString = strConnectionString;
        }

        public List<string> GetTalbeNameArray()
        {
            using (var db = new SQLServerDAO(ConnectionString))
            {
                var data = db.Query("SELECT * FROM sys.tables");
                return data.Select(entry => entry.Get<string>("name")).ToList();
            }
        }

        public List<string> GetViewNameArray()
        {
            using (var db = new SQLServerDAO(ConnectionString))
            {
                var data = db.Query("SELECT * FROM sys.views");
                return data.Select(entry => entry.Get<string>("name")).ToList();
            }
        }

        public List<string> GetStoredProcedureNamesArray()
        {
            using (var db = new SQLServerDAO(ConnectionString))
            {
                var data = db.Query("SELECT * FROM sys.objects WHERE type= 'p'");
                return data.Select(entry => entry.Get<string>("name")).ToList();
            }
        }

        public TableObject GetTableInfomation(string strTableName)
        {
            #region /* SQL Text */
            var strSQL = @"SELECT   D.name as table_name, ISNULL(F.value, '') as table_desc, A.colorder as column_index, A.name as column_name, 
CASE WHEN COLUMNPROPERTY(A.ID,A.NAME, 'ISIDENTITY ') = 1 THEN 1 ELSE 0 END as is_identity, 
CASE WHEN EXISTS(
	SELECT 1 FROM SYSOBJECTS WHERE XTYPE= 'PK ' AND PARENT_OBJ=A.ID AND NAME IN ( 
		SELECT NAME FROM SYSINDEXES WHERE INDID IN(SELECT INDID FROM SYSINDEXKEYS WHERE ID = A.ID AND COLID=A.COLID
		)
	)
) THEN 1 ELSE 0 END AS is_pk, 
B.NAME as type_name, A.LENGTH as type_length, 
CASE WHEN A.ISNULLABLE=1 THEN 1 ELSE 0 END as is_null, ISNULL(E.TEXT, '') as column_def_val, 
ISNULL(G.[VALUE], '') as column_desc 
FROM   
SYSCOLUMNS   A 
LEFT   JOIN   
SYSTYPES   B   
ON   
A.XUSERTYPE=B.XUSERTYPE 
INNER   JOIN   
SYSOBJECTS   D   
ON   
A.ID=D.ID     AND   D.XTYPE= 'U '   AND     D.NAME <> 'DTPROPERTIES ' 
LEFT   JOIN   
SYSCOMMENTS   E   
ON   
A.CDEFAULT=E.ID 
LEFT   JOIN   
sys.extended_properties   G   
ON   
A.ID=G.major_id   AND   A.COLID=G.minor_id     
LEFT   JOIN   
sys.extended_properties   F   
ON   
D.ID=F.major_id   AND   F.minor_id=0 
WHERE d.name = {0}
ORDER   BY   
A.ID,A.COLORDER";
            #endregion

            using (var db = new SQLServerDAO(ConnectionString))
            {
                var data = db.Query(strSQL, strTableName);
                var table = new TableObject();

                table.Name = strTableName;

                data.ForEach(o => 
                {
                    var entry = new ColumnObject() 
                    {
                        Name = o.Get<string>("column_name"),
                        DbLength = o.Get<int>("type_length"),
                        IsNull = o.Get<int>("is_null") == 1,
                        IsPrimaryKey = o.Get<int>("is_pk") == 1,
                        DbType = o.Get<string>("type_name"),
                        IsIdentity = o.Get<int>("is_identity") == 1,
                        DefaultValue = o.Get<string>("column_def_val"),
                        Description = o.Get<string>("column_desc"),
                    };

                    table.Columns.Add(entry);
                });

                return table;
            }
        }

        public DataTable Execute(string strSqlText)
        {
            using (var db = new SQLServerDAO(ConnectionString))
            {
                var result = db.ExecuteDataSet(strSqlText);

                return result.Tables.Count > 0 ? result.Tables[0] : new DataTable();
            }
        }
    }
}
